const pool = require('../database/conn')
const {getHexByLength} = require("../utils/generateHex");
const getGroupInfoByGid = async (gid) => {
    const sql = 'SELECT * FROM `groups` where id = ?'
    const [rows] = await pool.query(sql, [gid])
    return rows
}

const call_sp_create_group = async (fields) => {
    const sql = 'CALL sp_create_group(?,?,?,?,?)'

    const [result] = await pool.query(sql, fields)
    return result
}

const getGroupMemberById = async (gid) => {
    const sql = "SELECT\n" +
        "\t`group_members`.*,\n" +
        "\t`users`.id,\n" +
        "\t`users`.name,\n" +
        "\t`users`.avatar \n" +
        "FROM\n" +
        "\t`group_members`\n" +
        "\tJOIN users ON `group_members`.user_id = `users`.id \n" +
        "WHERE\n" +
        "\tgroup_id = ?"

    const [rs] = await pool.query(sql, [gid])
    return rs
}

const getChannelByGid = async (gid) => {
    // const sql = "SELECT * FROM `channel` where `group_id` = ? "
    const sql = 'SELECT channel.*, channel_tag.tag_name \n' +
        'FROM `channel` \n' +
        'LEFT JOIN `channel_tag` ON `channel`.tag_id = `channel_tag`.id\n' +
        'WHERE `group_id` = ? order by `channel`.created_at asc'
    const [row] = await pool.query(sql, [gid])
    return row
}

const createChannel = async (incomingFields) => {
    const sql = "INSERT INTO `channel` (id, group_id, tag_id, name, icon) VALUES(?,?,?,?,?)"
    const [result] = await pool.query(sql, incomingFields)
    return  result
}

const updateGroup = async (sql, values) => {
    const [result] = await pool.query(sql, values)
    return result
}

const getChannelTag = async (gid) =>{
    const sql = 'SELECT * FROM `channel_tag` WHERE `gid` = ? '
    const [row] = await pool.query(sql, [gid])
    return row
}

const createChannelTag = async (gid, tagName) => {
    const sql = 'INSERT INTO `channel_tag` (gid, tag_name) VALUES (?, ?)'
    const [rs] = await pool.query(sql, [gid, tagName])
    return rs
}

module.exports = {
    getGroupInfoByGid,
    call_sp_create_group,
    updateGroup,
    getGroupMemberById,
    getChannelByGid,
    getChannelTag,
    createChannel,
    createChannelTag
}